package nckd.wnqjt.tmc.tda.plugin.form;

import kd.bos.algo.DataSet;
import kd.bos.algo.Row;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.entity.report.AbstractReportListDataPlugin;
import kd.bos.entity.report.ReportQueryParam;
import kd.bos.orm.query.QCP;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.QueryServiceHelper;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;

/**
 * @author zhujintao
 * @date 2024/11/05
 * @desc 资金总额按银行大类
 */
public class TotalFundsByBankDetailListDataPlugin extends AbstractReportListDataPlugin {

    @Override
    public DataSet query(ReportQueryParam reportQueryParam, Object o) throws Throwable {
        Map<String, Object> customParam = reportQueryParam.getCustomParam();
        /**
         * ***************************************************************
         */
        //获取当前日期起止日期
        Date startdate;
        Date enddate;
        if (customParam != null && customParam.get("nckd_querydate") != null) {
            String querydatestr = String.valueOf(customParam.get("nckd_querydate"));
            Date parseDate = null;
            try {
                parseDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(querydatestr);
            } catch (ParseException e) {
                throw new RuntimeException(e);
            }
            LocalDate localDate = LocalDate.from(LocalDateTime.ofInstant(parseDate.toInstant(), ZoneId.systemDefault()).toLocalDate());
            startdate = Date.from(localDate.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant());
            LocalDateTime enddateTime = localDate.atTime(23, 59, 59);
            enddate = Date.from(enddateTime.atZone(ZoneId.systemDefault()).toInstant());
        } else {
            //获取当前日期起止日期
            startdate = Date.from(LocalDate.now().atStartOfDay().atZone(ZoneId.systemDefault()).toInstant());
            LocalDateTime enddateTime = LocalDate.now().atTime(23, 59, 59);
            enddate = Date.from(enddateTime.atZone(ZoneId.systemDefault()).toInstant());
        }

        //子公司当前余额+母公司当前余额 排除结算中心
        QFilter finorgtype = new QFilter("number", QCP.equals, "FI-015");
        DynamicObject queryOne = QueryServiceHelper.queryOne("bd_finorgtype", "id", finorgtype.toArray());

        //先获取符合条件的账户列表
        QFilter acctstatusfilter = new QFilter("acctstatus", QCP.not_equals, "closed")
                .and(new QFilter("bank.finorgtype.id", QCP.not_equals, queryOne.getLong("id")))
                .and("opendate", QCP.less_equals, enddate);
        String getAcctSelectedFieds = "id,company,openorg,issetbankinterface,bank.bank_cate.id bankbankcate,bank.bank_cate.number bankbankcatenumber,bank.bank_cate.name bankbankcatename,bankaccountnumber,isvirtual,name,currencyname,defaultcurrency,opendate,acctstatus,acctstyle,accttype,acctproperty,manager,acctname,finorgtype";
        DataSet acctDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "bd_accountbanks", getAcctSelectedFieds, acctstatusfilter.toArray(), null);


        //子公司当前余额+母公司当前余额 排除结算中心
        QFilter beiBankbalanceFilter = new QFilter("bizdate", QCP.large_equals, startdate).and("bizdate", QCP.less_equals, enddate);
        if (queryOne != null) {
            Object pkValue = queryOne.getLong("id");
            beiBankbalanceFilter.and(new QFilter("bank.finorgtype.id", QCP.not_equals, pkValue));
        }
        String beiBankbalanceSelectField = "id,company.id companyid,company.number companynumber,company.name companyname" +
                ",accountbank.id accountbankid,accountbank.bankaccountnumber bankaccountnumber,bizdate,nckd_depositbalance,amount,freezebalance,datasource";
        DataSet beiBankbalanceDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "bei_bankbalance", beiBankbalanceSelectField, beiBankbalanceFilter.toArray(), null);

        DataSet parentAmount = getParentBankAccountNumberAmount(beiBankbalanceDataSet.copy());
        List<String> depositbalanceList = new ArrayList<>();
        DataSet parentAmountCopy = parentAmount.copy();
        while (parentAmountCopy.hasNext()) {
            Row next = parentAmountCopy.next();
            depositbalanceList.add(next.getString("parentcompanyaccount"));
        }

        //排除母账户，母账户的资金总额=母账户的当前余额-所有子账户的上存余额
        DataSet dataSet1 = beiBankbalanceDataSet.filter("bankaccountnumber in numbers", Collections.singletonMap("numbers", depositbalanceList));
        DataSet dataSet2 = beiBankbalanceDataSet.filter("bankaccountnumber not in numbers", Collections.singletonMap("numbers", depositbalanceList));
        dataSet1 = dataSet1.join(parentAmount).on("bankaccountnumber", "parentcompanyaccount").select(beiBankbalanceDataSet.getRowMeta().getFieldNames(), new String[]{"nckd_depositbalance depositbalance_sum"}).finish();
        dataSet1 = dataSet1.updateField("amount", "amount - depositbalance_sum");
        dataSet1 = dataSet1.removeFields("depositbalance_sum");
        DataSet union1 = dataSet1.union(dataSet2);

        //获取虚拟户
        DataSet filterAcctDataSet = acctDataSet.copy().filter("isvirtual = true");
        List<String> isvirtualAccountNoList = new ArrayList<>();
        while (filterAcctDataSet.hasNext()) {
            Row next = filterAcctDataSet.next();
            String bankaccountnumber = next.getString("bankaccountnumber");
            isvirtualAccountNoList.add(bankaccountnumber);
        }
        //受限资金管理
        String amRestrictedfundsmanagerSelectField = "id,org.id companyid,org.number companynumber,org.name companyname,bankacct.id accountbankid,bankacct.bankaccountnumber bankaccountnumber,unrestrictedamt";
        //增加受限
        QFilter businesstype = new QFilter("businesstype", QCP.equals, "1").and("billstatus", QCP.equals, "C")
                .and("bankacct.bankaccountnumber", QCP.in, isvirtualAccountNoList).and("restricteddate", QCP.less_equals, enddate);
        DataSet amRestrictedfundsmanagerDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "am_restrictedfundsmanager", amRestrictedfundsmanagerSelectField, businesstype.toArray(), null);
        amRestrictedfundsmanagerDataSet = amRestrictedfundsmanagerDataSet.groupBy(new String[]{"companyid", "bankaccountnumber"}).sum("unrestrictedamt").finish();
        //增加受限
        //QFilter businesstype1 = new QFilter("businesstype", QCP.equals, "1").and("billstatus", QCP.equals, "C")
        //        .and("restricteddate", QCP.less_equals, enddate);
        //DataSet amRestrictedfundsmanagerDataSet1 = QueryServiceHelper.queryDataSet(this.getClass().getName(), "am_restrictedfundsmanager", amRestrictedfundsmanagerSelectField, businesstype1.toArray(), null);
        //amRestrictedfundsmanagerDataSet1 = amRestrictedfundsmanagerDataSet1.groupBy(new String[]{"companyid", "bankaccountnumber"}).sum("unrestrictedamt").finish();
        //定期存款处理
        String cimDepositSelectField = "id,org.id companyid,org.number companynumber,org.name companyname,settleaccount.id accountbankid,settleaccount.bankaccountnumber bankaccountnumber,surplusamount";
        QFilter cimDepositQfilter = new QFilter("billstatus", QCP.equals, "C").and("finaccount", QCP.in, isvirtualAccountNoList).and("intdate", QCP.less_equals, enddate);
        DataSet cimDepositDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cim_deposit", cimDepositSelectField, cimDepositQfilter.toArray(), null);
        cimDepositDataSet = cimDepositDataSet.groupBy(new String[]{"companyid", "bankaccountnumber"}).sum("surplusamount").finish();

        //QFilter cimDepositQfilter1 = new QFilter("billstatus", QCP.equals, "C")
        //        .and("intdate", QCP.less_equals, enddate);
        //DataSet cimDepositDataSet1 = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cim_deposit", cimDepositSelectField, cimDepositQfilter1.toArray(), null);
        //cimDepositDataSet1 = cimDepositDataSet1.groupBy(new String[]{"companyid", "bankaccountnumber"}).sum("surplusamount").finish();

        DataSet finish1 = acctDataSet.leftJoin(union1).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
                .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "bankbankcatename", "isvirtual", "finorgtype"}, new String[]{"amount", "nckd_depositbalance", "freezebalance"}).finish();
        DataSet finish2 = finish1.leftJoin(amRestrictedfundsmanagerDataSet).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
                .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "bankbankcatename", "isvirtual", "finorgtype", "amount", "nckd_depositbalance", "freezebalance"}, new String[]{"unrestrictedamt"}).finish();
        DataSet finish = finish2.leftJoin(cimDepositDataSet).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
                .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "bankbankcatename", "isvirtual", "finorgtype", "amount", "nckd_depositbalance", "freezebalance", "unrestrictedamt"}, new String[]{"surplusamount"}).finish();
        List<String> bankbankcatenameList = (ArrayList) customParam.get("bankbankcatename");
        finish = finish.filter("bankbankcatename = '" + bankbankcatenameList.get(0) + "'");
        //DataSet finish4 = finish3.leftJoin(amRestrictedfundsmanagerDataSet1).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
        //        .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "isvirtual", "finorgtype", "amount", "freezebalance", "unrestrictedamt", "surplusamount"}, new String[]{"unrestrictedamt as unrestrictedamt1"}).finish();
        //DataSet finish = finish4.leftJoin(cimDepositDataSet1).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
        //        .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "isvirtual", "finorgtype", "amount", "freezebalance", "unrestrictedamt", "surplusamount", "unrestrictedamt1"}, new String[]{"surplusamount as surplusamount1"}).finish();

        finish = finish.updateField("amount", "case when amount is null then 0 else amount end");
        finish = finish.updateField("nckd_depositbalance", "case when nckd_depositbalance is null then 0 else nckd_depositbalance end");
        finish = finish.updateField("unrestrictedamt", "case when unrestrictedamt is null then 0 else unrestrictedamt end");
        finish = finish.updateField("surplusamount", "case when surplusamount is null then 0 else surplusamount end");

        finish = finish.addField("amount + nckd_depositbalance + unrestrictedamt + surplusamount", "totalfunds");
        finish = finish.addField("0", "sort").select("bankbankcate", "company", "bankaccountnumber", "totalfunds", "sort");

        finish = finish.filter("totalfunds > 0");

        DataSet total = finish.copy().groupBy(new String[]{"bankbankcate"}).sum("totalfunds").finish();
        total = total.addNullField("company").addField("1", "sort").addField("'小计'", "bankaccountnumber");
        total = total.select("bankbankcate", "company", "bankaccountnumber", "totalfunds", "sort");
        return finish.union(total).orderBy(new String[]{"bankbankcate", "sort"});
    }

    /**
     * 获取母账户下子账户所有的上存余额
     *
     * @param beiBankbalanceDataSetCopy
     * @return
     */
    private DataSet getParentBankAccountNumberAmount(DataSet beiBankbalanceDataSetCopy) {
        //母子账户组 fca_acctgroup
        String fcaAcctgroupSelectField = "id,company.id companyid,company.number companynumber,company.name companyname,accountbank.id accountbankid,accountbank.bankaccountnumber parentcompanyaccount, " +
                "entrys.bankacct.id bankacctid,entrys.bankacct.bankaccountnumber subsidiarybankaccount,entrys.bankacct.company.id bankacctcompanyid,entrys.bankacct.company.number bankacctcompanynumber,entrys.bankacct.company.name bankacctcompanyname";
        QFilter fcaacctgroupFilter = new QFilter("enable", QCP.equals, "1");
        //有效的母子账户组
        DataSet fcaAcctgroupDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "fca_acctgroup", fcaAcctgroupSelectField, fcaacctgroupFilter.toArray(), null);

        DataSet finish = fcaAcctgroupDataSet.join(beiBankbalanceDataSetCopy).on("subsidiarybankaccount", "bankaccountnumber")
                .select(new String[]{"parentcompanyaccount"}, new String[]{"nckd_depositbalance"})
                .finish().groupBy(new String[]{"parentcompanyaccount"}).sum("nckd_depositbalance").finish();

        return finish;
    }
}
